IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Delete'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Delete
GO

CREATE PROC dbo.pInProdDetail_Delete
  @id        INT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @oldAmount		INT,
    @productId		INT,
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	SELECT @oldAmount = Amount,
		@productId =  ProductId
	FROM InProdDetail
	WHERE Id = @id
	
	SET @intReturn = 1
													
	BEGIN TRANSACTION
	BEGIN TRY
		-- Delete in prod detail									
		DELETE FROM dbo.InProdDetail
		WHERE Id = @id	
		
		-- Update amount of product
		UPDATE Product
		SET Amount = (Amount - @oldAmount)
		WHERE Id = @productId
		
		-- Update latest input price of product
		EXEC pUpdateLatestInputPriceOfProduct
			@vintProductId = @productId
			
		SET @intReturn = 1
		
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SET @intReturn = 0
		PRINT 'Error'
	  ROLLBACK TRANSACTION
	  
	END CATCH
	
	SELECT Result = @intReturn
